﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace bezeq_online_work.model
{
    class DB_workSet
    {
        private string ConnectionString = bezeq_online_work.Properties.Settings.Default.Main_DBConnectionString.ToString();
        private SqlDataReader rdr = null;
        private SqlConnection con = null;


        private string translatore(int codename)
        {
            switch (codename)
            { 
                case 1:
                    return "BlockRequests";
                case 2:
                    return "Manager";
                case 3:
                    return "Moked";
                case 4:
                    return "Statistics";
                case 5:
                    return "TeamManager";
                case 6:
                    return "Worker";
                case 7:
                    return "WorkSettings";
                case 8:
                    return "WorksShift";
                case 9:
                    return "WorkerShiftsSet";
                default:
                    throw new Exception("bad-DataTable Code number Exception");
            
            }
        
        }

        private void establisConnection()
        { 
            // Open connection to the database
            con = new SqlConnection(ConnectionString);
            con.Open();
        }
      
        private void closeConnection()
        { 
              if (con.State == ConnectionState.Open)
               con.Close();
        }

        private List<string> GET(int id, List<string> col, List<string> conditions)
        { 
          SqlCommand cmd = null;
          string colums="";
          string conditionss="";
            string sup="";
          List<string> Answer = new List<string>();
          string table = translatore(id);
            try
            {
                establisConnection();

                // Set up a command with the given query and associate
                // this with the current connection.
             
                
                int i = 0;
                foreach (string colname in col)
                {
                    if (colname.CompareTo("") != 0)
                    {
                        colums = colums + colname;
                        if (i + 1 < col.Count)
                        { colums = colums + ","; }
                    }
                    i++;
                }

                i = 0;
                foreach (string cond in conditions)
                {
                    if (cond.CompareTo("") != 0)
                    {
                        conditionss = conditionss + cond;
                        if (i + 1 < conditions.Count)
                        { conditionss = conditionss + " AND "; }
                    }
                    i++;
                }

                //the final query string
                string CommandText = "SELECT " + colums + " FROM " + table + " WHERE " + conditionss+ " ;" ;

                cmd = new SqlCommand(CommandText);
                cmd.Connection = con;

                SqlDataReader reader = cmd.ExecuteReader();
                if (col.Count == 1 && col[0].CompareTo("*") == 0)
                {
                    while (reader.Read())
                    {
                        sup = "";
                        // sup = (string)(reader[0]);
                        for ( i = 0; i < reader.FieldCount ; i++)
                        { sup = sup +" | "+ reader[i]; }
                       
                        Answer.Add(sup);
                    }
                    
                }
                else
                {
                    while (reader.Read())
                    {
                        sup = "";
                        foreach (string colname in col)
                        { sup = sup + " | " +  reader[colname]; }
                        Answer.Add(sup);
                    }
                }
            }
            catch (Exception ex)
            {
                // throw error message
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                // Close data reader object and database connection
                    if (rdr != null)
                    { rdr.Close(); }
                closeConnection();
            }
            
            return Answer;
        }

        private void INPUT(int id,List<string> values)
        {
            string valuess = "";
            SqlCommand cmd = null;
            string table = translatore(id);
            try
            {

             establisConnection();
                // Set up a command with the given query and associate
                // this with the current connection.
             int i = 0;
             foreach (string field in values)
             {
                 if (field.CompareTo("") != 0)
                 {
                     valuess = valuess + field;
                     if (i + 1 < values.Count)
                     { valuess = valuess + ","; }
                 }
                 i++;
             }

             //the final query string
             string CommandText = "INSERT INTO " + table + " VALUES (" + valuess + ") ; ";
             
             cmd = new SqlCommand(CommandText);
             cmd.Connection = con;
             cmd.ExecuteNonQuery();
            
            }
            catch (Exception ex)
            {
               
               // throw error message
                throw new Exception(ex.ToString());
            }
            finally
            {
               if (cmd != null)
                    cmd.Dispose();
                // Close database connection   
                closeConnection();
            }
    
        }

        private void INPUT(int id, List<string> col, List<string> values)
        {
            string valuess = "";
            string colms = "";
            SqlCommand cmd = null;
            string table = translatore(id);
            try
            {

                establisConnection();
                // Set up a command with the given query and associate
                // this with the current connection.
                int i = 0;
                foreach (string field in values)
                {
                    if (field.CompareTo("") != 0)
                    {
                        valuess = valuess + field;
                        if (i + 1 < values.Count)
                        { valuess = valuess + ","; }
                    }
                    i++;
                }

                i = 0;
                foreach (string field in col)
                {
                    if (field.CompareTo("") != 0)
                    {
                        colms = colms + field;
                        if (i + 1 < values.Count)
                        { colms = colms + ","; }
                    }
                    i++;
                }

                //the final query string
                string CommandText = "INSERT INTO " + table + "("+colms+") VALUES (" + valuess + ") ; ";
                //string CommandText = "INSERT INTO BlockRequests  VALUES (33,676,'gggg','fgd') ;";
                cmd = new SqlCommand(CommandText);
                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {

                // throw error message
                throw new Exception(ex.ToString());
            }
            finally
            {
                  if (cmd != null)
                       cmd.Dispose();
                // Close database connection   
                closeConnection();
            }

        }

        private void UPDATE(int id, List<string> col_val, List<string> conditions)
        {
            string whoto_val = "";
            string condits = "";
            SqlCommand cmd = null;
            string table = translatore(id);
            
            try
            {
                establisConnection();

                int i = 0;
                foreach (string field in col_val)
                {
                    if (field.CompareTo("") != 0)
                    {
                        whoto_val = whoto_val + field;
                        if (i + 1 < col_val.Count)
                        { whoto_val = whoto_val + ","; }
                    }
                    i++;
                }

                i = 0;
                foreach (string field in conditions)
                {
                    if (field.CompareTo("") != 0)
                    {
                        condits = condits + field;
                        if (i + 1 < conditions.Count)
                        { condits = condits + ","; }
                    }
                    i++;
                }
                //the final query string
                string CommandText = "UPDATE  " + table + " SET "+whoto_val+" WHERE " + condits + " ; ";
                //string CommandText = "INSERT INTO BlockRequests  VALUES (33,676,'gggg','fgd') ;";
                cmd = new SqlCommand(CommandText);
                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                // throw error message
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                // Close database connection   
                closeConnection();
            }

        }

        private void DELETE(int id, List<string> conditions )
        {
            string condits = "";
            SqlCommand cmd = null;
            string table = translatore(id);
            try
            {
                establisConnection();
                int i = 0;
                foreach (string field in conditions)
                {
                    if (field.CompareTo("") != 0)
                    {
                        condits = condits + field;
                        if (i + 1 < conditions.Count)
                        { condits = condits + ","; }
                    }
                    i++;
                }
                //the final query string
                string CommandText = "DELETE FROM " + table + " WHERE " + condits + " ; ";
                //string CommandText = "INSERT INTO BlockRequests  VALUES (33,676,'gggg','fgd') ;";
                cmd = new SqlCommand(CommandText);
                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                // throw error message
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                // Close database connection   
                closeConnection();
            }

        }

        public List<string> getManager(List<string> col, List<string> condition)
        {
            return GET(2, col, condition);
        }

        public List<string> getTeamManager(List<string> col, List<string> condition)
        {
            return GET(5, col, condition);
        }

        public List<string> getWorker(List<string> col, List<string> condition)
        {
            return GET(6, col, condition);
        }

        public List<string> getSettings(List<string> col, List<string> condition)
        {
            return GET(7, col, condition);
        }

        public List<string> getBlockRequest(List<string> col, List<string> condition)
        {
            return GET(1, col, condition);
        }

        public void insertBlockRequest (List<string> values)
        {
            INPUT(1 , values);
        }

        public void deleteBlockRequest (List<string> conditions )
        {
            DELETE(1, conditions);
        }

        public List<string> getWorkerShiftsSet(List<string> col, List<string> conditions)
        {
            return GET(9, col, conditions);
        }
    }
}
